package net.huashitong.Indicators.dao;

import java.io.File;
import java.util.List;
import java.util.Map;

import javassist.bytecode.Mnemonic;

import org.hibernate.SQLQuery;
import org.omg.CORBA.OMGVMCID;
import org.springframework.stereotype.Repository;

import net.huashitong.Indicators.entity.MonthlyCount;
import net.huashitong.Indicators.entity.TertiaryIndustry;
import net.huashitong.Indicators.filter.MonthlyCountFilter;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;

@Repository
public class MonthlyCountDao extends StringDao<MonthlyCount>{
	
	
	//获得街镇，工业，各县市指标名
	public List<MonthlyCount> queryIndName(String aab001)
	{
		StringBuffer hql =new StringBuffer();
		hql.append("select DISTINCT(a.aab004 ),");//DISTINCT(aab004 ),aab003,aab005,aab006
		hql.append("a.aab001, ");
		hql.append("a.aab002 ,");
		hql.append("a.aab003,");
		hql.append("a.aab005,aab006 ");
		hql.append("from t_gis_monthly_count a ");
		hql.append("where a.aab001 =?");
		SQLQuery sql = getSession().createSQLQuery(hql.toString());
		sql.setParameter(0, aab001);
		List list =sql.list();
		return list;
		
	}
	public List<MonthlyCount> getIndInfo(String aab005,String bab001 )
	{
		GenericQuery query =createQuery("obj");
		query.append(" and obj.aab005=:aab005").setParam("aab005", aab005);
		query.append(" and obj.bab001=:bab001").setParam("bab001", bab001);
		
		return query.listResult();
	}
	public List<MonthlyCount> getInfoCount(String aab001,String aab003,String bab001){
		GenericQuery query = createQuery("obj");
		query.append(" and obj.aab001 =:aab001").setParam("aab001", aab001);
		//if(!"HB001".equals(aab001.trim())&& !"HB005".equals(aab001.trim()))
		if("HB004".equals(aab001.trim())||"HB002".equals(aab001.trim()))
		query.append(" and obj.aab003 =:aab003").setParam("aab003", aab003);
		query.append(" and obj.bab001 =:bab001 ").setParam("bab001", bab001);
		return query.listResult();
	}
	
	public List getCountName(String aab001)
	{
		StringBuffer hql =new StringBuffer();
		hql.append("select DISTINCT( aab003) aab003, aab004,aab001,aab002 ");
		hql.append(" from t_gis_monthly_count ");
		hql.append("where aab001 =?");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setParameter(0, aab001);
		
		return query.list();
		
		
		
	}
	
	public List  getNewMonth(String id ){
		StringBuffer hql =new StringBuffer();
		hql.append("select bab001 from t_gis_monthly_count where aab001 =? ORDER BY bab001 DESC LIMIT 1 ");
		
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setParameter(0, id);
		
		return query.list();
	}
	
	public List  getNewMon(String id ){
		StringBuffer hql =new StringBuffer();
		hql.append("select bab001 from t_gis_monthly_count where aab005 =? ORDER BY bab001 DESC LIMIT 1 ");
		
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setParameter(0, id);
		
		return query.list();
	}
	
	public List<MonthlyCount> getHistoryData(MonthlyCountFilter filter){
		GenericQuery query =createQuery("obj");
		if("".equals(filter.getAab005())|| filter.getAab005() ==null){
			query.append(" and aab003 =:aab003").setParam("aab003",filter.getAab003());
			query.append(" and aab005=:aab005").setParam("aab005", "");
		}else {
			query.append(" and aab005 =:aab005").setParam("aab005", filter.getAab005());
		}
		return query.listResult();
		
	}
	/**
	 * 查询统计名称列表
	 * @return
	 */
	public List queryCountNames()
	{
		StringBuffer hql =new StringBuffer();
		//hql.append(" SELECT aab001,aab002,aab003,aab004,bab001  FROM  t_gis_monthly_count GROUP BY aab004;");
		hql.append(" select  t.aab001  , t.aab002  ,t.aab001 aab003, t.aab002 aab004 ,(select b.bab001 from t_gis_monthly_count b  where b.aab001 =t.aab001 ORDER BY bab001 DESC  LIMIT 1) bab001  from t_gis_monthly_count  t where LENGTH(t.aab001 )= 5  and t.aab001 not in ('HB004','HB002') GROUP BY aab001 ");
		hql.append(" union" );
		hql.append(" 		 select   c.aab001,   c.aab002,  c.aab003,  c.aab004 ,(select d.bab001 from t_gis_monthly_count d  where d.aab001 =c.aab001 ORDER BY d.bab001 DESC  LIMIT 1) bab001 from t_gis_monthly_count c where LENGTH(  c.aab001 )= 5 and   c.aab001 in  ('HB004' ,'Hb002') ");
		
	    SQLQuery query = getSession().createSQLQuery(hql.toString());
		return query.list();
	}

	//获取二级数据
	public List queryTwoClassData(MonthlyCountFilter filter )
	{

		GenericQuery query =createQuery("obj");
		query.append(" and aab003 like:aab003 ").setParam("aab003",filter.getAab003()+"%");
		query.append(" and bab001 =:bab001").setParam("bab001",filter.getBab001());
		//query.append(" and aab005 =:aab005").setParam("aab005","");
		query.append(" group by aab003 ");
		return query.listResult();
	}
	//获取三级数据
	public List queryThirdClassData(String id,String bab001 )
	{
		GenericQuery query =createQuery("obj");
		query.append(" and aab005 like:aab005  ").setParam("aab005",id+"%");
		query.append(" and bab001 =:bab001").setParam("bab001",bab001);

		return query.listResult();
	}

	
}
